package com.neuedu.his.dao;

import com.neuedu.his.po.UmsUser;
import com.neuedu.his.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 金山
 * 项目：jdbc-dao
 * site: https://blog.fulfill.com.cn
 * 描述  ums_user表的数据访问对象
 * @data 2024/11/1114:07
 */
public class UmsUserDao extends BaseDao {


    /**
     * 1 查询集合
     *
     * @param sql    select username,user_id from ums_user where username like '??'
     * @param sql    select * from ums_user where dept_id =  ?
     *               select * from ums_user  where username = ? and password  = ?
     * @param params
     * @return
     */
    public List<UmsUser> selectAll(String sql, Object... params) {

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List list = new ArrayList();
        try {
            conn = DBUtils.getConnection();
            stmt = conn.prepareStatement(sql);


            // 绑定变量
            DBUtils.bingStmtVar(stmt, params);

            // 执行查询
            rs = stmt.executeQuery();
            while (rs.next()) {

                long userid = rs.getLong("userid");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String nickname = rs.getString("nickname");
                Date expire = rs.getDate("expire");
                Date lastLogin = rs.getDate("lastlogin");
                long deptId = rs.getLong("dept_id");
                String deptName = rs.getString("dept_name");
                long registLevel = rs.getLong("regist_level");
                String del = rs.getString("del");
                Date createtime = rs.getDate("createtime");

                UmsUser user = new UmsUser(
                        userid, username, password, nickname, expire, lastLogin, deptId, deptName, registLevel, del, createtime
                );

                list.add(user);

            }


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        ;

        return list;
    }

    public UmsUser selectById(long id) {

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        UmsUser user = null;
        try {
            conn = DBUtils.getConnection();
            String sql = "select * from ums_user where userid= ?";
            stmt = conn.prepareStatement(sql);


            // 绑定变量
            DBUtils.bingStmtVar(stmt, id);

            // 执行查询
            rs = stmt.executeQuery();
            if (rs.next()) {

                Long userid = rs.getLong("userid");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String nickname = rs.getString("nickname");
                Date expire = rs.getDate("expire");
                Date lastLogin = rs.getDate("lastlogin");
                Long deptId = (Long) rs.getObject("dept_id");
                String deptName = rs.getString("dept_name");
                Long registLevel = (Long) rs.getObject("regist_level");
                String del = rs.getString("del");
                Date createtime = rs.getDate("createtime");

                user = new UmsUser(
                        userid, username, password, nickname, expire, lastLogin, deptId, deptName, registLevel, del, createtime
                );


            }


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


        return user;
    }


    public int total(String sql, Object... params) {

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int count = 0;
        try {
            conn = DBUtils.getConnection();
            stmt = conn.prepareStatement("select count(1) c from (" + sql + ") tab  ");

            // 绑定变量
            DBUtils.bingStmtVar(stmt, params);
            // 执行查询
            rs = stmt.executeQuery();

            // 定位到数据行
            if (rs.next()) {
                count = rs.getInt("c");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }


    public List<UmsUser> selectPage(int current, int size, String sql, Object... params) {

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List list = new ArrayList();
        try {
            conn = DBUtils.getConnection();

            int start = (current - 1) * size;
            int len = size;
            String limitSql = "select *  from ("+sql+") tab limit  "+start+"  , "+len;
            System.out.println("分页sql: "+limitSql);
            stmt = conn.prepareStatement(limitSql);


            // 绑定变量
            DBUtils.bingStmtVar(stmt, params);

            // 执行查询
            rs = stmt.executeQuery();
            while (rs.next()) {

                long userid = rs.getLong("userid");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String nickname = rs.getString("nickname");
                Date expire = rs.getDate("expire");
                Date lastLogin = rs.getDate("lastlogin");
                long deptId = rs.getLong("dept_id");
                String deptName = rs.getString("dept_name");
                long registLevel = rs.getLong("regist_level");
                String del = rs.getString("del");
                Date createtime = rs.getDate("createtime");

                UmsUser user = new UmsUser(
                        userid, username, password, nickname, expire, lastLogin, deptId, deptName, registLevel, del, createtime
                );

                list.add(user);

            }


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return list;
    }


    public int insert(UmsUser user) {
        String sql = "INSERT INTO  `ums_user` (   `username`,  `password`,  `nickname`, `expire`, `lastlogin`, `dept_id`, `dept_name`, `regist_level` )  values(?,?,?,?,?,?,?,?)";
        int count = super.update(sql,
                user.getUsername(),
                user.getPassword(),
                user.getNickname(),
                user.getExpire(),
                user.getLastLogin(),
                user.getDeptId(),
                user.getDeptName(),
                user.getRegistLevel()
        );

        return count;

    }


    public int update(UmsUser user) {
        StringBuffer sql = new StringBuffer();

        sql.append(" update 					");
        sql.append("   `ums_user` 			");
        sql.append(" set			");
        sql.append("  		        	");
        sql.append("   `username` = ?,			");
        sql.append("   `password` = ?,			");
        sql.append("   `nickname` = ?,			");
        sql.append("   `expire` = ?,		");
        sql.append("   `lastlogin` = ?,			");
        sql.append("   `dept_id` = ?,			");
        sql.append("   `dept_name` = ?,			");
        sql.append("   `regist_level` = ?,			");
        sql.append("   `del` = ?			");
        sql.append(" where `userid` =  ?			");


        int count = super.update(sql.toString(),
                user.getUsername(),
                user.getPassword(),
                user.getNickname(),
                user.getExpire(),
                user.getLastLogin(),
                user.getDeptId(),
                user.getDeptName(),
                user.getRegistLevel(),
                user.getDel(),
                user.getUserid()
        );

        return count;

    }

    /**
     * 通用的方法
     *
     * @return
     */

    public int delete(long id) {
        StringBuffer sql = new StringBuffer();

        sql.append(" delete from ums_user where userid = ?");

        int count = super.update(sql.toString(),   id        );

        return count;

    }


}
